﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using TAMS;

namespace TAMS
{
    public partial class editHousing : System.Web.UI.Page
    {
        MySqlConnection conn;
        MySqlCommand sqlCommand;
        MySqlDataReader readVals;
        string strProvider = Resource1.databaseConnection;
        string type;
        string id;
        string enteredName;

        protected void Page_Load(object sender, EventArgs e)
        {
            // Connect to the Database
            conn = new MySqlConnection(strProvider);
            conn.Open();
            string query;

            // Get the type
            type = Request.QueryString["type"];
            id = Request.QueryString["id"];
            enteredName = Request.QueryString["name"];

            if (!IsPostBack)
            {
                
                if (type == "staff")
                {
                    string getQuery = "SELECT* FROM Housing h, Staff S, FResides FR" +
                            " WHERE S.fsid = FR.fsid AND h.hid = FR.hid AND S.fsid=" + id;

                    query = "SELECT H.hid as 'Housing ID', H.h_building as 'Building Name', H.doorNo as 'Door Number', H.h_telNo as 'Phone Number' FROM Housing H" +
                            " WHERE H.hid NOT IN (SELECT hid FROM Staff S, FResides FR" +
                            " WHERE S.fsid = FR.fsid )";

                             
                    sqlCommand = new MySqlCommand(getQuery, conn);
                    readVals = sqlCommand.ExecuteReader();
                    readVals.Read();


                    Label1.Text = "Housing info for, id: " + id + " and Name: " + enteredName;

                    if (readVals.HasRows)
                    {
                        Label2.Text = "" + readVals.GetString("h_building");
                        Label3.Text = "" + readVals.GetString("doorNo");
                        Label4.Text = "" + readVals.GetString("h_telNo");
                        Label5.Text = "" + readVals.GetDateTime("fr_since").ToString("dd-MM-yyyy");
                    }
                    else
                    {
                        Label2.Text = "" + "No Data";
                        Label3.Text = "" + "" + "No Data";
                        Label4.Text = "" + "" + "No Data";
                        Label5.Text = "" + "" + "No Data";
                    }

                    readVals.Close();

                    sqlCommand = new MySqlCommand(query, conn);
                    readVals = sqlCommand.ExecuteReader();

                    GridView1.DataSource = readVals;
                    GridView1.DataBind();

                }
                else if (type == "student")
                {
                    string getQuery = "SELECT* FROM Housing h, GradStudent S, SResides FR" +
                            " WHERE S.sid = FR.sid AND h.hid = FR.hid AND S.sid=" + id;

                    query = "SELECT H.hid as 'Housing ID', H.h_building as 'Building Name', H.doorNo as 'Door Number', H.h_telNo as 'Phone Number' FROM Housing H" +
                            " WHERE H.hid NOT IN (SELECT hid FROM GradStudent S, SResides FR" +
                            " WHERE S.sid = FR.sid )";


                    sqlCommand = new MySqlCommand(getQuery, conn);
                    readVals = sqlCommand.ExecuteReader();
                    readVals.Read();

                    Label1.Text = "Housing info for, id: " + id + " and Name: " + enteredName;

                    if (readVals.HasRows)
                    {
                        Label2.Text = "" + readVals.GetString("h_building");
                        Label3.Text = "" + readVals.GetString("doorNo");
                        Label4.Text = "" + readVals.GetString("h_telNo");
                        Label5.Text = "" + readVals.GetDateTime("sr_since").ToString("dd-MM-yyyy");
                    }
                    else
                    {
                        Label2.Text = "" + "No Data";
                        Label3.Text = "" + "" + "No Data";
                        Label4.Text = "" + "" + "No Data";
                        Label5.Text = "" + "" + "No Data";
                    }


                    readVals.Close();

                    sqlCommand = new MySqlCommand(query, conn);
                    readVals = sqlCommand.ExecuteReader();

                    GridView1.DataSource = readVals;
                    GridView1.DataBind();
                }

            }
            
 
        }

        protected void change_clicked(object sender, EventArgs e)
        {
            string query;

            GridViewRow clickedRow = ((Button)sender).NamingContainer as GridViewRow;
            
            // id yi alıyor cell in 1. indexi
            string key = clickedRow.Cells[1].Text;

            if (type == "staff")
            {
                string date = (DateTime.Now).ToString("yyyy-MM-dd");

                string getQuery = "SELECT* FROM Housing h, Staff S, FResides FR" +
                                  " WHERE S.fsid = FR.fsid AND h.hid = FR.hid AND S.fsid=" + id;

                sqlCommand = new MySqlCommand(getQuery, conn);
                readVals = sqlCommand.ExecuteReader();
                readVals.Read();

                if (readVals.HasRows)
                {
                    query = "UPDATE FResides "
                            + "SET  hid=" + "'" + key + "' ," + " fr_since=" + "'" + date + "'"
                            + " WHERE fsid=" + id;
                }
                else
                {
                    query = "INSERT  "
                            + "INTO  FResides"
                            + " VALUES(" + "'" + date + "', " +  key + ", "  + id + ")";
                }
                readVals.Close();


                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editHousing.aspx?" + "name=" + enteredName + "&type=staff&id=" + id;

                Response.Redirect(url);

            }
            else if (type == "student")
            {
                string date = (DateTime.Now).ToString("yyyy-MM-dd");

                string getQuery = "SELECT* FROM Housing h, GradStudent S, SResides FR" +
                            " WHERE S.sid = FR.sid AND h.hid = FR.hid AND S.sid=" + id;

                sqlCommand = new MySqlCommand(getQuery, conn);
                readVals = sqlCommand.ExecuteReader();
                readVals.Read();

                if (readVals.HasRows)
                {
                    query = "UPDATE SResides "
                            + "SET  hid=" + "'" + key + "' ," + " sr_since=" + "'" + date + "'"
                            + " WHERE sid=" + id;
                }
                else
                {
                    query = "INSERT  "
                             + "INTO  SResides"
                             + " VALUES(" + "'" + date + "', " + key + ", " + id + ")";
                }
                readVals.Close();

                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editHousing.aspx?"+ "name="+enteredName +"&type=student&id=" + id;

                Response.Redirect(url);
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
                        if (type == "staff")
            {
                string query =  "DELETE  "
                                + "FROM  FResides"
                                + " WHERE fsid=" + id;
           
                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editHousing.aspx?" + "name=" + enteredName + "&type=staff&id=" + id;

                Response.Redirect(url);
            }
            else if(type == "student")
            {
                string query = "DELETE  "
                + "FROM  SResides"
                + " WHERE sid=" + id;

                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editHousing.aspx?" + "name=" + enteredName + "&type=student&id=" + id;

                Response.Redirect(url);
            }
        }
       }

    }
